package internationalenergytradingplatform.dao;

import com.github.pagehelper.Page;
import internationalenergytradingplatform.VO.SupplierPageQueryVO;
import internationalenergytradingplatform.dto.SupplierPageQueryDTO;
import internationalenergytradingplatform.pojo.Employee;
import internationalenergytradingplatform.pojo.SupplierAccount;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface SupplierAccountDao {
    /**
     *
     * @author yjh
     * @date 2024/2/13 21:39
    根据供应商公司账号（手机号）名称查询供应商公司账号
     */
    @Select("select * from supplier_accountr_tb where tele = #{tele}")
    SupplierAccount getCompanyByTele(String tele);

    /**
     *
     * @author yjh
     * @date 2024/2/14 22:40
     在数据库中插入一条供应商数据
     */
    @Insert("INSERT INTO supplier_accountr_tb VALUES (#{tele}, #{password}, #{companyName}, #{unifiedCreditCode}, #{unifiedCreditImage}, #{avatarImage}, #{supplierAccountStatus})")
    Integer addSupplierAccount(@Param("tele") String tele, @Param("password") String password, @Param("companyName") String companyName, @Param("unifiedCreditCode") String unifiedCreditCode, @Param("unifiedCreditImage") String unifiedCreditImage, @Param("avatarImage") String avatarImage, @Param("supplierAccountStatus") Integer supplierAccountStatus);

    /**
     *
     * @author yingfeng
     * @date 2024/2/29 17:27
     插入员工数据(因为配置文件开启了驼峰命名转化匹配，就没用@Param注解)
     */
    @Insert("INSERT INTO supplier_employee_tb VALUES(#{tele}, #{name}, #{accountStatus}, #{peopleAdd}, #{finalOperator}, #{finalOperatorTime}, #{company})")
    void addEmployee(Employee employee);

    /**
     *
     * @author yingfeng
     * @date 2024/3/1 23:08
     查询今日营业额
     */
    @Select("SELECT SUM(c.price * o.number) AS total_price " +
            "FROM order_tb o " +
            "JOIN commodity_tb c ON o.commodity = c.id " +
            "WHERE DATE(o.order_time) = #{nowData} and (o.supplier) = #{tele} " +
            "GROUP BY DATE(o.order_time);")
    Double getTurnover(@Param("tele") String tele, @Param("nowData") String nowData);

    /**
     *
     * @author yingfeng
     * @date 2024/3/2 0:20
     查询当日新增有效订单
     */
    @Select("select count(id) from INTERNATIONAL_ENERGY_CONNECT_DB.order_tb " +
            "where date(order_time) = #{nowData} and supplier = #{tele};")
    Integer getNewOrderQuantity(@Param("tele") String tele, @Param("nowData") String nowData);

    /**
     *
     * @author yingfeng
     * @date 2024/3/2 0:25
    查询所有运输中的订单
     */
    @Select("select count(id) from INTERNATIONAL_ENERGY_CONNECT_DB.order_tb " +
            "where status = 2 and supplier = #{tele};")
    Integer getOrdersInTransit(@Param("tele") String tele);

    /**
     *
     * @author yingfeng
     * @date 2024/3/2 0:31
    查询低分评价的数量
     */
    @Select("select count(INTERNATIONAL_ENERGY_CONNECT_DB.commodity_reviews_tb.id) from \n" +
            "INTERNATIONAL_ENERGY_CONNECT_DB.commodity_reviews_tb left join INTERNATIONAL_ENERGY_CONNECT_DB.commodity_tb\n" +
            "on INTERNATIONAL_ENERGY_CONNECT_DB.commodity_reviews_tb.commodity = INTERNATIONAL_ENERGY_CONNECT_DB.commodity_tb.id \n" +
            "where INTERNATIONAL_ENERGY_CONNECT_DB.commodity_reviews_tb.score <= 2 and \n" +
            "INTERNATIONAL_ENERGY_CONNECT_DB.commodity_tb.supplier_account = #{tele};")
    Integer getNewPositiveReviews(@Param("tele") String tele);

    /**
     *
     * @author yingfeng
     * @date 2024/3/2 0:31
    查询高分评价的数量
     */
    @Select("select count(INTERNATIONAL_ENERGY_CONNECT_DB.commodity_reviews_tb.id) from \n" +
            "INTERNATIONAL_ENERGY_CONNECT_DB.commodity_reviews_tb left join INTERNATIONAL_ENERGY_CONNECT_DB.commodity_tb\n" +
            "on INTERNATIONAL_ENERGY_CONNECT_DB.commodity_reviews_tb.commodity = INTERNATIONAL_ENERGY_CONNECT_DB.commodity_tb.id \n" +
            "where INTERNATIONAL_ENERGY_CONNECT_DB.commodity_reviews_tb.score >= 4 and \n" +
            "INTERNATIONAL_ENERGY_CONNECT_DB.commodity_tb.supplier_account = #{tele};")
    Integer getNewlyAddedNegativeReviews(@Param("tele") String tele);

    /**
     *
     * @author yingfeng
     * @date 2024/3/2 15:44
    查询各种状态的订单数量（1）
     */
    @Select("select count(id) from INTERNATIONAL_ENERGY_CONNECT_DB.order_tb " +
            "where supplier = #{tele} " +
            "and status = #{status};")
    Integer getOrderData(@Param("tele") String tele, @Param("status") Integer status);

    /**
     *
     * @author yingfeng
     * @date 2024/3/2 15:55
     查询全部订单数量
     */
    @Select("select count(id) from INTERNATIONAL_ENERGY_CONNECT_DB.order_tb " +
            "where supplier = #{tele} ")
    Integer getAllCountOrder(@Param("tele") String tele);

    /**
     *
     * @author yingfeng
     * @date 2024/3/3 13:14
     查询已上架/已下架的商品
     */
    @Select("SELECT count(id)  " +
            "FROM INTERNATIONAL_ENERGY_CONNECT_DB.commodity_tb " +
            "where commodity_status = #{state} and supplier_account = #{tele};")
    Integer getCommodityData(@Param("tele") String tele, @Param("state") Integer state);

    /**
     *
     * @author yingfeng
     * @date 2024/3/3 13:14
    查询已上架/已下架的套餐
     */
    @Select("SELECT count(id)  " +
            "FROM INTERNATIONAL_ENERGY_CONNECT_DB.package_tb " +
            "where sales_status = #{state} and supplier_accountr = #{tele};")
    Integer getPackageData(@Param("tele") String tele, @Param("state") Integer state);

    /**
     * 查询所有供应商
     * @return
     */
    @Select("select * from supplier_accountr_tb")
    List<SupplierAccount> getAllSupplier();

    /**
     *
     * @author yingfeng
     * @date 2024/4/25 22:11
     更改供应商状态
     */
    @Update("UPDATE supplier_accountr_tb SET supplier_account_status = #{state} WHERE tele = #{tele}")
    Integer updateSupplierAccountState(@Param("tele") String tele, @Param("state") Integer state);

    /**
     *
     * @author yingfeng
     * @date 2024/4/25 22:12
     查询供应商账号余额
     */
    @Select("SELECT " +
            "SUM(CASE WHEN type = 3 THEN `values` ELSE 0 END) - SUM(CASE WHEN type = 2 THEN `values` ELSE 0 END) AS final_balance " +
            "FROM " +
            "supplier_balance_change_tb where account = #{tele};")
    Double getBanlance(String tele);

    /**
     *
     * @author yingfeng
     * @date 2024/4/25 22:34
     在余额流水表中加入取出余额的数据
     */
    @Insert("insert into supplier_balance_change_tb values(null, #{time}, 2, #{money}, null, #{tele})")
    void withdrawalMoney(@Param("time") String time, @Param("money") Double money, @Param("tele") String tele);

    /**
     *
     * @author yingfeng
     * @date 2024/4/26 15:35
    在余额流水表中加入存入余额的数据
     */
    @Insert("insert into supplier_balance_change_tb values(null, #{time}, 3, #{money}, null, #{tele})")
    void savingMoney(@Param("time") String time, @Param("money") Double money, @Param("tele") String tele);
}
